<?php
/*
 * inc-question.php
 * Retral/CSCD494 Winter 2009
 * Eastern Washington University
 *
 * File Description:
 * Gives detailed information on a question and provides links for edits to be made
 * 
 * Other:
 * To Display admin approval settings "&qAproval" needs to be in the URL
 */
?>


<link rel='stylesheet'
	href="css/preview.css"
	type="text/css" media="screen">

<?php
//include ( 'jpgraph/src/jpgraph.php'); 
//include ('jpgraph/src/jpgraph_line.php'); 
//include ( 'jpgraph/src/Examples/example0.php'); 

/*
 * Gives detailed information on a question and allows edits to be made.
 */

	require("KinID.php");

	$conn = oci_connect(ORA_CON_UN, ORA_CON_PW, ORA_CON_DB);
	

	//add user comments to the database
	if(isset($_POST['userComments']))
	{
			$userID = $_SESSION['userID'];
			$qComments = $_POST['userComments'];
			$qid = $_REQUEST['id'];
			$ver = $_REQUEST['ver'];			
		
			$queryComments = "INSERT INTO question_comment(USER_ID, QUESTION_ID, Q_VERSION, COMMENT_DATE, COMMENTS )VALUES (:userID, :qid, :ver, SYSDATE, :qComments)";	
			$stmt = oci_parse($conn, $queryComments)or die("stmt");
			oci_bind_by_name($stmt, ":userID", $userID)or die("b1");
			oci_bind_by_name($stmt, ":qid", $qid) or die("b2");
			oci_bind_by_name($stmt, ":ver", $ver)or die("b3");
			oci_bind_by_name($stmt, ":qComments", $qComments)or die("b4");

			if(!oci_execute($stmt))
				print "<p>Failed to add comments.</p>";

			oci_free_statement($queryComments);
	}
	
	
	

	$query = '
		SELECT question_type.type_description, question_content.points, 
		     question_content.question_title, question.date_modified, 
		     ape_user.first_name, ape_user.last_name
		FROM question_content INNER JOIN question 
		ON question.question_content_id=question_content.question_content_id
		INNER JOIN question_type 
		ON question.question_type_code=question_type.type_code
		INNER JOIN ape_user ON question_content.author_id=ape_user.user_id
		WHERE question_id=:QID AND q_version = :vNum';

	$id = $_REQUEST['id'];
	$v = $_REQUEST['ver'];
	
	$stmt = oci_parse($conn, $query);
	oci_bind_by_name($stmt, ':QID', $id);
	oci_bind_by_name($stmt, ':vNum', $v);
	if(!oci_execute($stmt))
		print "<P>Could not query database for Question $id";

	$basicInfo = oci_fetch_assoc($stmt);
	if(!$basicInfo)
		print "<P>Could not find Question $id</P>";

	oci_free_statement($stmt);
	
	$query = '
		SELECT AVG(grade) AS "AVG"
		FROM student_question WHERE question_id=:QID AND q_version=:vNum';

	$stmt = oci_parse($conn, $query);
	oci_bind_by_name($stmt, ':QID', $id);
	oci_bind_by_name($stmt, ':vNum', $v);

	if(!oci_execute($stmt))
		print "<P>Students are unknown</P>";

	$row = oci_fetch_assoc($stmt);
	$avg = $row["AVG"];

	oci_free_statement($stmt);
	

	
	/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// this will grab the question scores
	$query = '
		SELECT REVISER_USER_ID FROM QUESTION WHERE QUESTION_ID=:QID';
		
    $reviserid = array();
	$stmt = oci_parse($conn, $query);
	oci_bind_by_name($stmt, ':QID', $id);
	
    	if(!oci_execute($stmt))
		print "<P>Could not query database for Question $id";
	
	while ($row = oci_fetch_array($stmt)) {
		array_push($reviserid,$row[0]);
	}	
	
	oci_free_statement($stmt);

	$ydata = array();
	$query = 'SELECT GRADE FROM STUDENT_QUESTION WHERE QUESTION_ID=:QID AND Q_VERSION=:QVER';
	$stmt = oci_parse($conn, $query);
	oci_bind_by_name($stmt, ':QID', $id);
	oci_bind_by_name($stmt, ':QVER', $v);
	
    if(!oci_execute($stmt))
		print "<P>Could not query database for Question $id";

	while ($row = oci_fetch_array($stmt)) {
		$ydata[] = $row['GRADE'];
		
	}	
	
	oci_free_statement($stmt);
	
	sort($ydata);

	if($ydata)
	{
		$_SESSION['ydata'] = $ydata;
	}
	else
	{
		$_SESSION['ydata'] = array(0,0);
	}

	
			
	// Set $minVal = 5 if not set, otherwise use current val
	if (!isset($_REQUEST['min']))
		$minVal = 5;
	else
		$minVal = $_REQUEST['min'];
		

	print "<span class='header-text'>Question Approval</span><br/>\n";
	
	
	echo "<table width=\"100%\" style=\"float:right\"><tr><td width=\"50%\">";

	$query = "SELECT Q_REVISION FROM QUESTION WHERE question_id=:qid AND q_version = :qver";
	$rev = 0;
	$stmt = oci_parse($conn, $query);
	oci_bind_by_name($stmt, ':qid', $id);
	oci_bind_by_name($stmt, ':qver', $v);

	if(!oci_execute($stmt))
		print "<P>Could not query database for Question $id";
	$row = oci_fetch_assoc($stmt);
	$rev = $row['Q_REVISION'];
	$isCurrentVers = false;
	if($rev>0)	
	{
		echo "<h3><a href='?page=addQuestionHome&id=" . $id . "&ver=" . $v ."'> Create New Revision </a></h3>";
		$isCurrentVers = true;
	}
	
	
	if($_SESSION['userID'] == $reviserid[0] && !$isCurrentVers)
	{
		echo "<h3><strong> Title: ".$basicInfo['QUESTION_TITLE']. "</strong> <a href='?page=editType&id=" . $id . "&ver=" . $v ."&min=$minVal'> &nbsp; edit </a></h3>";
	}
	else
	{
		echo "<h3><strong> Title: ".$basicInfo['QUESTION_TITLE']. "</strong> </h3>";
	}
	
	
	
	
	
	echo "Author: ".$basicInfo['FIRST_NAME']." ".
	      $basicInfo['LAST_NAME']."<br>\n";
	echo "Created: ".dateFormat($basicInfo['DATE_MODIFIED']);
	echo "\n<BR>Revision: $rev";

		echo "\n<BR>Type: " . $basicInfo['TYPE_DESCRIPTION']; // . "<a href='?page=editType&id=" . $id . "&ver=" . $v ."&min=$minVal'> &nbsp; edit </a>";
		echo "\n<BR>Points: " . $basicInfo['POINTS']; // . "<a href='?page=editType&id=" . $id . "&ver=" . $v ."&min=$minVal'> &nbsp; edit </a>";
	

	
	$sid = 0;
    $isfile = true;
	$temp = getInstructions($conn, $sid, $isfile);
    
    echo '<br><br>';
	
	if($_SESSION['userID'] == $reviserid[0] && !$isCurrentVers)
	{
	    if($isfile)
	        echo "\n<h3>Instructions: <a href='?page=viewSource&id=" . $sid . "&qid=" . $id. "&ver=" . $v . "&min=$minVal'> &nbsp; edit </a></h3>";
		else
	        echo "\n<h3>Instructions: <a href='?page=addSource&qid=" . $id. "&ver=" . $v . "&kin=" . instr() .  "&min=$minVal'> &nbsp; add instructions </a></h3>";
	}
	else
	{
	    if($isfile)
	        echo "\n<h3>Instructions: </h3>";
		else
	        echo "\n<h3>Instructions: </h3>";
	}

	

        
    if($temp != null)
        echo "\n<P>". $temp ."</p>";
    else
        echo "<P>No Instruction Result</P>";
	
	$sid = 0;
    $isfile = true;
	$temp = getCriteria($conn, $sid, $isfile);
    
    echo '<br><br>';
	if($_SESSION['userID'] == $reviserid[0] && !$isCurrentVers)
	{
	    if($isfile)
	        echo "\n<h3>Scoring Criteria: <a href='?page=viewSource&id=" . $sid . "&qid=" . $id. "&ver=" . $v . "&min=$minVal'> &nbsp; edit </a></h3>";
		else
	        echo "\n<h3>Scoring Criteria: <a href='?page=addSource&qid=" . $id. "&ver=" . $v . "&kin=" . scoring() .  "&min=$minVal'> &nbsp; add scoring criteria </a></h3>";
	}
	else
	{
	    if($isfile)
	        echo "\n<h3>Scoring Criteria: </h3>";
		else
	        echo "\n<h3>Scoring Criteria: </h3>";
	}

		

    
    if($temp != null)
        echo "\n<P>". $temp ."</p>";
    else
        echo "<P>No criteria Result</P>";
		
	//these are for the graphs in the question window()JPGRAPH LIBRARY
	echo "</td><td><img src=\"jpgraph/src/Examples/example01.php\"><img src=\"jpgraph/src/Examples/example0.php\"><img src=\"jpgraph/src/Examples/example02.php\"></td></tr></table>";

	//here is the source file information ---------------------------------------------------------------------------------
	$query = '
		SELECT source_file_id, source_file_name, kin FROM source_file 
		WHERE question_id=:QID AND q_version=:vNum 
		     AND kin != :KIN1 AND kin != :KIN2';

	$stmt = oci_parse($conn, $query);
	oci_bind_by_name($stmt, ':QID', $id);
	oci_bind_by_name($stmt, ':vNum', $v);
	oci_bind_by_name($stmt, ':KIN1', scoring());
	oci_bind_by_name($stmt, ':KIN2', instr());

	if(!oci_execute($stmt))
		print "<p><b>Error: (Question) - SQL Statement failed to execute.</b></p>";

	echo '<br><br>';
	if($_SESSION['userID'] == $reviserid[0])
	{
		echo "\n<h3>Source Files : <a href='?page=addSource&qid=" . $id . "&ver=" . $v . "&min=$minVal'> &nbsp; add source file </a></h3>";

	}
	else
	{
		echo "\n<h3>Source Files : </h3>";	
	}
	

	$studentInput = array();
	$studentOutput = array();
	$graderInput = array();
	$graderOutput = array();
	$sourceFiles = array();
	
	$rows = 0;
    while($row = oci_fetch_assoc($stmt)) 
    {
		if($row['KIN']==33)
		{	//displays link
			$studentInput[] = "<a href='?page=viewSource&id=" . $row['SOURCE_FILE_ID'] . "&qid=" . $id. "&ver=" . $v . "&name=" . $row['SOURCE_FILE_NAME'] . "&min=$minVal'>".$row['SOURCE_FILE_NAME']."</a><BR>\n";
			//display without link
		}
		else if($row['KIN']==65)
		{
			$studentOutput[] = "<a href='?page=viewSource&id=" . $row['SOURCE_FILE_ID'] . "&qid=" . $id. "&ver=" . $v . "&name=" . $row['SOURCE_FILE_NAME'] . "&min=$minVal'>".$row['SOURCE_FILE_NAME']."</a><BR>\n";
		}
		else if($row['KIN']==32)
		{
			$graderInput[] = "<a href='?page=viewSource&id=" . $row['SOURCE_FILE_ID'] . "&qid=" . $id. "&ver=" . $v . "&name=" . $row['SOURCE_FILE_NAME'] . "&min=$minVal'>".$row['SOURCE_FILE_NAME']."</a><BR>\n";
		}
		else if($row['KIN']==64)
		{
			$graderOutput[] = "<a href='?page=viewSource&id=" . $row['SOURCE_FILE_ID'] . "&qid=" . $id. "&ver=" . $v . "&name=" . $row['SOURCE_FILE_NAME'] . "&min=$minVal'>".$row['SOURCE_FILE_NAME']."</a><BR>\n";
		}
		else if($row['KIN']==3)
		{
			$sourceFiles[] = $row['SOURCE_FILE_NAME'];
		}
		//echo "<a href='?page=viewSource&id=" . $row['SOURCE_FILE_ID'] . "&qid=" . $id. "&ver=" . $v . "&name=" . $row['SOURCE_FILE_NAME'] . "&min=$minVal'>";
		//echo $row['SOURCE_FILE_NAME']."</a><BR>\n";
        $rows++;
	}
    if($rows == 0)
        echo "No source files found for this question.";
	else
	{
		echo "<b>Student Input Files:</b><br>";
		foreach($studentInput as $value)
		{
			echo $value."<br>";
		}
		echo "<b>Student Output Files:</b><br>";
		foreach($studentOutput as $value)
		{
			echo $value."<br>";
		}
		echo "<b>Grader Input Files:</b><br>";
		foreach($graderInput as $value)
		{
			echo $value."<br>";
		}
		echo "<b>Grader Output Files:</b><br>";
		foreach($graderOutput as $value)
		{
			echo $value."<br>";
		}
		echo "<b>Source Files:</b><br>";
		foreach($sourceFiles as $value)
		{
			echo $value."<br>";
		}

	}

	oci_free_statement($stmt);

	
	//Display Admin Approval options	
	if(!displayCRs($conn))
	{
		//Display User Comments for each question
		echo "\n<h3>User Comments: </h3>";
		$query = "SELECT question_comment.comments, question_comment.comment_date, ape_user.first_name, ape_user.last_name FROM question_comment 
		INNER JOIN ape_user ON question_comment.user_id = ape_user.user_id
		where question_comment.question_id = :qid AND question_comment.q_version=:qver";
		$stmt = oci_parse($conn, $query);
		oci_bind_by_name($stmt, ':QID', $id);
		oci_bind_by_name($stmt, ':qver', $v);
		
		if(!oci_execute($stmt))
			print "<p><b>Error: (Question) - SQL Statement failed to execute.</b></p>";
			
		while($row = oci_fetch_assoc($stmt)) 
		{
			echo "<fieldset  style='width:450px' ><legend>".$row['FIRST_NAME']." ".$row['LAST_NAME']." ".$row['COMMENT_DATE']."</legend>";
			echo $row['COMMENTS'];
			echo "</fieldset>";
			$commentCount++;
		}
		if($commentCount ==0)
			echo "User Comments have not been left for this question.";
			
		echo "<h3>Add Comments: </h3>";
		echo "<form method='POST'>";
		echo "<textarea name='userComments'  style='width: 300px' WRAP='SOFT' ></textarea><br>";
		echo "<input type='submit' value='Add'/>";
		echo "<input type='hidden' name='commentQuestionID' value=".$id."/>";
		echo "<input type='hidden' name='commentVersion' value=".$v."/>";
		echo "</form>";
	}
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////	
	
	
	oci_close($conn);
	oci_free_statement($stmt);
	
	
	
	
	
	
	
	
	
function getInstructions($conn, &$instrID, &$isfile) 
{
	$query = '
		SELECT source_file, source_file_id FROM source_file
		WHERE kin = :KIN AND question_id = :QID AND q_version = :vNum';

	$id = $_REQUEST['id'];
	$v = $_REQUEST['ver'];

	$stmt = oci_parse($conn, $query);
	oci_bind_by_name($stmt, ':QID', $id);
	oci_bind_by_name($stmt, ':vNum', $v);
	oci_bind_by_name($stmt, ':KIN', instr());
	if(!oci_execute($stmt))
		print "<P>instruction query failed</P>";

	$result = oci_fetch_assoc($stmt);
	if(!$result)
    {
        $isfile = false;
        return null;
    }
	$instrID = $result["SOURCE_FILE_ID"];
	return $result["SOURCE_FILE"]->load();
}

function getCriteria($conn, &$instrID, &$isfile) 
{
	$query = '
		SELECT source_file, source_file_id FROM source_file
		WHERE kin = :KIN AND question_id = :QID AND q_version = :vNum';

	$id = $_REQUEST['id'];
	$v = $_REQUEST['ver'];

	$stmt = oci_parse($conn, $query);
	oci_bind_by_name($stmt, ':QID', $id);
	oci_bind_by_name($stmt, ':vNum', $v);
	oci_bind_by_name($stmt, ':KIN', scoring());
	if(!oci_execute($stmt))
		print "<P>criteria query failed</P>";

    $result = oci_fetch_assoc($stmt);
	if(!$result)
    {
        $isfile = false;
        return null;
    }	
	$instrID = $result["SOURCE_FILE_ID"];
	return $result["SOURCE_FILE"]->load();

}

/* Add revision options for admin users only */
function displayCRs($conn) 
{
	if (isset($_SESSION['loggedIn']) && $_SESSION['roleCode'] == "admin" && isset($_REQUEST['qAproval'] ) )
	{
		$id = $_REQUEST['id'];
		$ver = $_REQUEST['ver'];

		// This is the value in the database that the database uses
		// to signify an admin response revision. Meaning of the 
		// numbers are not documented here because it will not
		// get updated.
		$responseValue = 2;
		$requestValue = 1;

		$query = '
			SELECT COUNT(question_id) AS "COUNT" FROM change_request
			INNER JOIN revision_type 
			ON change_request.request_code=revision_type.request_code
			WHERE question_id=:QID AND q_version =:qver AND revision_type.rev_type = :RQ';
		$stmt = oci_parse($conn, $query);
		oci_bind_by_name($stmt, ':QID', $id);
		oci_bind_by_name($stmt, ':qver', $ver);
		oci_bind_by_name($stmt, ':RQ', $requestValue);
		if(!oci_execute($stmt))
			print "<p>Bad query for CR count</p>";

		$result = oci_fetch_assoc($stmt);

		if($result['COUNT'] > 0)
		{
			print "<h3>Change Requests ".$result['COUNT']."</h3>";
			
			$query = '
				SELECT comments, q_version, date_modified,
				     type_description, request_code 
				FROM change_request NATURAL JOIN revision_type 
                NATURAL JOIN question 
                WHERE revision_type.rev_type = :RQ
                AND question_id=:QID AND q_version=:qver
                ORDER BY date_modified DESC'; 

			$stmt = oci_parse($conn, $query);
			oci_bind_by_name($stmt, ':QID', $id);
			oci_bind_by_name($stmt, ':qver', $ver);
			oci_bind_by_name($stmt, ':RQ', $requestValue);
			if(!oci_execute($stmt))
				print "<p>Bad query for CR information</p>";

			$query = '
				SELECT request_code, type_description 
				FROM revision_type WHERE rev_type >= :RSP
				ORDER BY type_description';

			$stmtOption = oci_parse($conn,$query);
			oci_bind_by_name($stmtOption, ':RSP', $responseValue);
			if (!oci_execute($stmtOption))
				echo "<p><b>Error: (Question - Revision dropdown)</b> SQL statement failed to execute</p>";

			$options = "";
			//Walk through DB listing each option that was requested
			while ($option = oci_fetch_assoc($stmtOption))
			{
				$options .= "\t<option value='".$option['REQUEST_CODE']."'>";
				$options .= $option['TYPE_DESCRIPTION']."</option>\n";
			}
			oci_free_statement($stmtOption);

			while($row = oci_fetch_assoc($stmt))
			{

				print dateFormat($row['DATE_MODIFIED'])."<br>\n";
				print $row['TYPE_DESCRIPTION']."<br>\n";

				print "<form action='index.php' method='POST'>\n";
				print "<input type='hidden' name='page' value='request'>\n";
				print "<input type='hidden' name='qid' value='$id'>\n";
				print "<input type='hidden' name='qver' value='".$row['Q_VERSION']."'>\n";
				print "<input type='hidden' name='cr' value='".$row['REQUEST_CODE']."'>\n";
				
				echo "<label style='text-align: left; vertical-align: top;' for='questionComment'>
					<strong>Comments</strong></label>&nbsp;
					<textarea name='questionComment'  rows='5' cols='30'>Enter Comments here.</textarea><br><p>";
				
				echo "<b>Revision Decision:</b> \n";
				echo "<select name='rev' id='rev'>";
			
				echo $options;
					
				echo "</select>";
				echo "<input type='submit' value='Mark Question'>";
				echo "</form><hr>";
			}

			oci_free_statement($stmt);
		}
		//echo "here";
		return true;
	}

	return false;
}
?>

<script type="text/javascript"
	src="script/preview.js" language="javascript"></script>
